package mybatis.dao;

import estore.web_servlet.PersonBean;
import mybatis.yang.*;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Mybatis {

    public SqlSessionFactory getSqlSesionFactory() throws Exception{
        String resource = "Conf/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }
    //注册用户添加到数据库
    public void add_regist(PersonBean person) throws Exception{
        SqlSessionFactory sqlSessionFactory=getSqlSesionFactory();
        SqlSession openSession=sqlSessionFactory.openSession();
        try{
            EmployeeMapper mapper=openSession.getMapper(EmployeeMapper.class);
            //增加数据
            boolean bol=mapper.addEmp(person);
            System.out.println(bol);
            openSession.commit();
        }finally {
            openSession.close();
        }
    }
    //查询登录用户是否存在数据库
    public boolean select(login_xinxi loginxinxi) throws Exception{
        SqlSessionFactory sqlSessionFactory=getSqlSesionFactory();
        SqlSession openSession=sqlSessionFactory.openSession();
        try{
            EmployeeMapper mapper=openSession.getMapper(EmployeeMapper.class);
            //查询数据
            int s= mapper.getEmpByusernameandBypassword(loginxinxi);
            openSession.commit();
            if(s>0){
                return true;
            }else{
                return false;
            }

        }finally {
            openSession.close();
        }
    }
    //将添加的图书放入数据库
    public boolean addBook(Book book) throws Exception{
        SqlSessionFactory sqlSessionFactory=getSqlSesionFactory();
        SqlSession openSession=sqlSessionFactory.openSession();
        try{
            EmployeeMapper mapper=openSession.getMapper(EmployeeMapper.class);
            //查询数据
            boolean s= mapper.addbook(book);
            openSession.commit();
            return s;
        }finally {
            openSession.close();
        }
    }
    //查询所有商品信息
    public List<Book> selectBook() throws SQLException {
        List<Book> book=new ArrayList<Book>();
        Connection con = null;
        PreparedStatement ps = null;
        java.sql.ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/bookstore","root","991010");
            String sql = "SELECT product_name,product_price,product_num,product_picture,product_miaoshu FROM product";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Book books = new Book();
                books.setProduct_name(rs.getString(1));
                books.setProduct_price(rs.getString(2));
                books.setProduct_num(rs.getString(3));
                books.setProduct_picture(rs.getString(4));
                books.setProduct_miaoshu(rs.getString(5));
                book.add(books);
            }
            return book;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) {
                con.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return book;
    }
    public Book selectBook2(String name) throws SQLException {
        Book book=new Book();
        Connection con = null;
        PreparedStatement ps = null;
        java.sql.ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/bookstore","root","991010");
            String sql="SELECT product_name,product_price,product_num,product_picture,product_miaoshu FROM product WHERE product_name=?";
            ps = con.prepareStatement(sql);
            ps.setString(1,name);
            rs = ps.executeQuery();
            while (rs.next()) {
                book.setProduct_name(rs.getString(1));
                book.setProduct_price(rs.getString(2));
                book.setProduct_num(rs.getString(3));
                book.setProduct_picture(rs.getString(4));
                book.setProduct_miaoshu(rs.getString(5));
            }
            return book;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) {
                con.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return book;
    }

    public Books selectBookid(String name) throws SQLException {
        Books book=new Books();
        Connection con = null;
        PreparedStatement ps = null;
        java.sql.ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/bookstore","root","991010");
            String sql = "SELECT * FROM product WHERE product_name=?";
            ps = con.prepareStatement(sql);
            ps.setString(1,name);
            rs = ps.executeQuery();
            System.out.println(name);
            while (rs.next()) {
                book.setProduct_name(rs.getString(1));
                book.setProduct_price(rs.getString(2));
                book.setProduct_num(rs.getString(3));
                book.setProduct_picture(rs.getString(4));
                book.setProduct_miaoshu(rs.getString(5));
                book.setId(rs.getInt(6));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) {
                con.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return book;
    }

    //购物车中转表里面加商品id和购物车固定id
    public void insert_order_product_id(String oder_id,String product_id) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        java.sql.ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/bookstore","root","991010");
            String sql = "INSERT INTO order_product(order_id,product_id) VALUE(?,?)";
            ps = con.prepareStatement(sql);
            ps.setString(1,oder_id);
            ps.setString(2,product_id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) {
                con.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
    }
    //查询出order_product里面的商品id和num,orderid固定为9哈,查不查随便
    public List<Order_product> select_order_product() throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        java.sql.ResultSet rs = null;
        List<Order_product> list=new ArrayList<Order_product>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/bookstore","root","991010");
            String sql = "SELECT * FROM order_product";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Order_product order=new Order_product();

                order.setOrder_id(rs.getString(1));
                order.setProduct_id(rs.getString(2));
                order.setNum(rs.getString(3));
                list.add(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) {
                con.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return list;
    }



    //查询order表里面的数据
    public List<Order> Select_order() throws SQLException {
        List<Order> list=new ArrayList<Order>();
        Connection con = null;
        PreparedStatement ps = null;
        java.sql.ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/bookstore","root","991010");
            String sql = "SELECT * FROM orders";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Order order=new Order();
                order.setOrder_product_id(rs.getString(1));
                order.setMoney(rs.getString(2));
                order.setAddress(rs.getString(3));
                order.setUser_id(rs.getString(4));
                list.add(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) {
                con.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return list;
    }

    //向order表里面添加数据
    public void insert_order(Order order) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        java.sql.ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/bookstore","root","991010");
            String sql = "INSERT INTO orders(order_product_id,money,address,user_id) VALUE(?,?,?,?)";
            ps = con.prepareStatement(sql);
            ps.setString(1,order.getOrder_product_id());
            ps.setString(2,order.getMoney());
            ps.setString(3,order.getAddress());
            ps.setString(4,order.getUser_id());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) {
                con.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
    }


    //查询对应商品id的价格
    public String selectBookID(String id) throws SQLException {
        String price = null;
        Connection con = null;
        PreparedStatement ps = null;
        java.sql.ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/bookstore","root","991010");
            String sql = "SELECT product_price FROM product WHERE id=?";
            ps = con.prepareStatement(sql);
            ps.setString(1,id);
            rs = ps.executeQuery();
            while (rs.next()) {
                price=rs.getString(1);
                return price;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) {
                con.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return price;
    }
}
